using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class KhachHangDAO
    {
        private KhachHangDTO[] ConvertDTtoArr(DataTable dt)
        {
            int n = dt.Rows.Count;
            KhachHangDTO[] KH = new KhachHangDTO[n];
            for (int i = 0; i < n; i++)
            { 
                KH[i] = new KhachHangDTO();
                KH[i].MaKH = Convert.ToInt32(dt.Rows[i]["MaKH"].ToString());
                KH[i].TenKH = dt.Rows[i]["TenKH"].ToString();
                KH[i].DienThoai = dt.Rows[i]["DienThoai"].ToString();
                KH[i].DiaChi = dt.Rows[i]["DiaChi"].ToString();
            }
            return KH;
        }
        public KhachHangDTO[] LayDanhSachKH()
        {
            KhachHangDTO[] KH = new KhachHangDTO[100];
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                DataTable dt = new DataTable();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_layDSKhachHang";
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                KH = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return KH;
        }

        public void ThemKhachHang(string TenKH, string DienThoai, string DiaChi)
        {
            KhachHangDTO khDTO = new KhachHangDTO();
            khDTO.TenKH = TenKH;
            khDTO.DienThoai = DienThoai;
            khDTO.DiaChi = DiaChi;
            ThemKhachHang(khDTO);
        }

        private void ThemKhachHang(KhachHangDTO kh)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemKH";

                cmd.Parameters.Add("@TenKH",SqlDbType.NVarChar);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);

                cmd.Parameters["@TenKH"].Value = kh.TenKH;
                cmd.Parameters["@DienThoai"].Value = kh.DienThoai;
                cmd.Parameters["@DiaChi"].Value = kh.DiaChi;

                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException ex)
            { 
                throw ex;
            }
        }
        public void CapNhatKhachHang(KhachHangDTO kh)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_CapNhatKhachHang";

                cmd.Parameters.Add("@MaKH", SqlDbType.Int);
                cmd.Parameters.Add("@TenKH", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);

                cmd.Parameters["@MaKH"].Value = kh.MaKH;
                cmd.Parameters["@TenKH"].Value = kh.TenKH;
                cmd.Parameters["@DienThoai"].Value = kh.DienThoai;
                cmd.Parameters["@DiaChi"].Value = kh.DiaChi;

                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        public KhachHangDTO[] TimKiemKhachHang(int MaKH)
        {
            KhachHangDTO[] kh = new KhachHangDTO[100];
            try
            {
                DataTable dt = new DataTable();
                string strMaKH = " 1 = 1 ";
                if (MaKH != 0)
                    strMaKH = " MaKH = " + MaKH;
                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();
                string strSQL = "SELECT * FROM KhachHang WHERE ";
                strSQL += strMaKH;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                kh = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return kh;
        }
        public KhachHangDTO[] TimKiemNangCao(KhachHangDTO kh)
        {
            KhachHangDTO[] arr = new KhachHangDTO[100];
            try
            {
                DataTable dt = new DataTable();
                string strTenKH = " 1 = 1 ";
                string strDienThoai = " 1 = 1 ";
                string strDiaChi = " 1 = 1 ";
                
                if (string.Compare(kh.TenKH, "") != 0)
                    strTenKH = " TenKH like N'%" + kh.TenKH + "%'";
                if (string.Compare(kh.DienThoai, "") != 0)
                    strDienThoai = " DienThoai like N'%" + kh.DienThoai + "%'";
                if (string.Compare(kh.DiaChi, "") != 0)
                    strDiaChi = " DiaChi like N'%" + kh.DiaChi + "%'";

                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();
                string strSQL = "SELECT * FROM KhachHang WHERE ";
                strSQL += strTenKH + " AND ";
                strSQL += strDienThoai + " AND ";
                strSQL += strDiaChi;
                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                arr = ConvertDTtoArr(dt);
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return arr;
        }

        public int ThemKhachHang_Output(int MaKH, string TenKH, string DienThoai, string DiaChi)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemKhachHang_Output";


                cmd.Parameters.Add("@MaKH", SqlDbType.Int);
                cmd.Parameters.Add("@TenKH", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NChar);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar);

                cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
                cmd.Parameters["@TenKH"].Value = TenKH;
                cmd.Parameters["@DienThoai"].Value = DienThoai;
                cmd.Parameters["@DiaChi"].Value = DiaChi;

                cmd.ExecuteNonQuery();
                MaKH = Convert.ToInt32(cmd.Parameters["@ID"].Value.ToString());
                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return MaKH;
        }
    }
}
